<?php
// menggunakan class phpExcelReader
include "../../config/excel_reader2.php";
require_once('../../config/koneksi.php');
// membaca file excel yang diupload
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);

// membaca jumlah baris dari data excel
$baris = $data->rowcount($sheet_index=0);

// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;

// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
for ($i=2; $i<=$baris; $i++)
{
  // membaca data kode (kolom ke-1)
  $kode = $data->val($i, 1);
  $nama = $data->val($i, 2);
  $bpjs = $data->val($i, 3);
	
	$cek = mysql_num_rows(mysql_query("select id_penduduk from penduduk where nik = '$kode'"));
	if($cek > 0){
		$query = "update penduduk set bpjs = '$bpjs' where nik = '$kode'";
		$sukses++;
	}else{
		$gagal++;
		$data_gagal[$gagal] = array(
			"nik" => "$kode",
			"nama" => "$nama",
			"bpjs" => "$bpjs"
		);
	}
}
?>
<?php
	if($gagal > 0){
	  require_once('../../export/Worksheet.php');
	  require_once('../../export/Workbook.php');
	  // koneksi ke mysql

	  // function untuk membuat header file excel
	  function HeaderingExcel() {
		  $tgl = date("dmY");
		  header("Content-type: application/vnd.ms-excel");
		  header("Content-Disposition: attachment; filename=Sinkronisasi_Gagal.xls" );
		  header("Expires: 0");
		  header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
		  header("Pragma: public");
		  }

	  // membuat header file excel dan nama filenya
	  HeaderingExcel('Data Peserta BPJS.xls');

	  // membuat workbook baru
	  $workbook = new Workbook("");
	  // membuat worksheet ke-1 
	  $worksheet1 =& $workbook->add_worksheet('Data Peserta BPJS');

	  // setting format header tabel data
	  $format =& $workbook->add_format();
	  $format->set_align('vcenter');
	  $format->set_align('center');
	  $format->set_color('white');
	  $format->set_bold();
	  $format->set_italic();
	  $format->set_pattern();
	  $format->set_fg_color('green');
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(0, 15);
	  $worksheet1->set_column(0, 0, 20);
	  $worksheet1->write_string(0, 0, "Sukses");
	  $worksheet1->set_column(0, 1, 20);
	  $worksheet1->write_number(0, 1, "$sukses");
	  
	  $worksheet1->set_row(1, 15);
	  $worksheet1->set_column(1, 0, 20);
	  $worksheet1->write_string(1, 0, "Gagal");
	  $worksheet1->set_column(1, 1, 20);
	  $worksheet1->write_number(1, 1, "$gagal");
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(0, 15);
	  $worksheet1->set_column(3, 0, 20);
	  $worksheet1->write_string(3, 0, "NIK", $format);
	  $worksheet1->set_column(3, 1, 20);
	  $worksheet1->write_string(3, 1, "NAMA", $format);
	  $worksheet1->set_column(3, 2, 30);
	  $worksheet1->write_string(3, 2, "BPJS", $format);

	  // menampilkan data mhasiswa laki-laki
	  $baris = 4;
	  for($i=1;$i<=$gagal;$i++){
			$no++;
			$worksheet1->write_string($baris, 0, $data_gagal[$no]['nik']);
			$worksheet1->write_string($baris, 1, $data_gagal[$no]['nama']);
			$worksheet1->write_string($baris, 2, $data_gagal[$no]['bpjs']);
			$baris++;
	  }

	  $workbook->close();
	}else{
		echo "<h3>Proses import data selesai.</h3>";
		echo "<p>Jumlah data yang sukses diimport : ".$sukses."<br>";
		echo "Jumlah data yang gagal diimport : ".$gagal."</p>";
	}
?>